package thirdwork.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import thirdwork.dao.EmployeeDao;
import thirdwork.dbutils.DBUtils;
import thirdwork.domain.Employee;

public class EmployeeImpl implements EmployeeDao {
	Connection conn = DBUtils.getConnection();
	PreparedStatement ps = null;

	@Override
	public List<Employee> selectAll() {
		Employee em = null;
		List lists = new ArrayList<Employee>();
		try {
			ps = conn.prepareStatement("select * from employee");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				em = new Employee();
				em.setId(rs.getInt("id"));
				em.setName(rs.getString("name"));
				em.setSex(rs.getString("sex"));
				em.setAge(rs.getInt("age"));
				em.setDepartment(rs.getString("department"));
				em.setType(rs.getString("type"));
				em.setYear(rs.getInt("year"));
				lists.add(em);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.closeAll();
		}
		return lists;
	}

	public Employee findById(int id) {
		Employee em = null;
		try {
			ps = conn.prepareStatement("select * from employee where id=?");
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				em = new Employee();
				em.setId(rs.getInt("id"));
				em.setName(rs.getString("name"));
				em.setSex(rs.getString("sex"));
				em.setAge(rs.getInt("age"));
				em.setDepartment(rs.getString("department"));
				em.setType(rs.getString("type"));
				em.setYear(rs.getInt("year"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.closeAll();
		}
		return em;
	}

	@Override
	public int updateEmp(Employee emp, int empid) {
		// TODO Auto-generated method stub
		int rs = 0;
		try {
			String sql = "update employee set name=?,sex=?,age=?,department=?,type=?,year=? where id=?";
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, emp.getName());
			pst.setString(2, emp.getSex());
			pst.setInt(3, emp.getAge());
			pst.setString(4, emp.getDepartment());
			pst.setString(5, emp.getType());
			pst.setInt(6, emp.getYear());
			pst.setInt(7, empid);
			rs = pst.executeUpdate();
			System.out.println("改动了" + rs + "行");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.closeAll();
		}
		return rs;
	}

	int result = 0;

	@Override
	public int deleteEmp(int id) {
		try {
			String sql = "delete from employee where id=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			result = pstmt.executeUpdate();// 返回sql执行结果
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.closeAll();
		}
		return result;
	}

	@Override
	public int insertEmp(Employee emp) {
		try {
			String sql = "insert into employee (name,sex,age,department,type,year) values (?,?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, emp.getName());
			pstmt.setString(2, emp.getSex());
			pstmt.setInt(3, emp.getAge());
			pstmt.setString(4, emp.getDepartment());
			pstmt.setString(5, emp.getType());
			pstmt.setInt(6, emp.getYear());
			int result = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtils.closeAll();
		}
		return result;
	}
}
